<?php
/**
 * 执行SQL优化脚本
 */

echo "==========================================\n";
echo "  客户表业务字段优化执行\n";
echo "==========================================\n\n";

try {
    // 尝试从ThinkPHP配置获取数据库连接信息
    $configFile = __DIR__ . '/../config/database.php';
    if (file_exists($configFile)) {
        include $configFile;
        // 尝试获取默认数据库配置
        if (isset($config) && isset($config['default'])) {
            $dbConfig = $config[$config['default']];
        } elseif (isset($database)) {
            $dbConfig = $database['default'] ?? $database;
        }
    }

    // 如果没有配置文件，使用默认配置
    if (!isset($dbConfig)) {
        echo "未找到数据库配置，使用默认配置...\n";
        $dbConfig = [
            'hostname' => '127.0.0.1',
            'username' => 'root',
            'password' => '',
            'database' => 'oa',
        ];
    }

    echo "数据库配置:\n";
    echo "  主机: " . ($dbConfig['hostname'] ?? $dbConfig['host'] ?? '127.0.0.1') . "\n";
    echo "  用户名: " . ($dbConfig['username'] ?? 'root') . "\n";
    echo "  数据库: " . ($dbConfig['database'] ?? 'oa') . "\n\n";

    // 提示输入密码
    echo "请输入数据库密码（如果没有密码直接按回车）: ";
    $password = trim(fgets(STDIN));

    // 建立数据库连接
    $host = $dbConfig['hostname'] ?? $dbConfig['host'] ?? '127.0.0.1';
    $username = $dbConfig['username'] ?? 'root';
    $database = $dbConfig['database'] ?? 'oa';

    echo "\n正在连接数据库...\n";
    $pdo = new PDO(
        "mysql:host=$host;dbname=$database;charset=utf8mb4",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );
    echo "✓ 数据库连接成功\n\n";

    // 读取SQL文件
    $sqlFile = __DIR__ . '/update_customer_business_fields.sql';
    if (!file_exists($sqlFile)) {
        throw new Exception("SQL文件不存在: $sqlFile");
    }

    $sql = file_get_contents($sqlFile);
    echo "✓ 读取SQL文件成功\n";
    echo "  文件大小: " . number_format(filesize($sqlFile) / 1024, 2) . " KB\n\n";

    // 分割SQL语句
    $statements = array_filter(
        array_map('trim', explode(';', $sql)),
        function($stmt) {
            return !empty($stmt) && !preg_match('/^(--|\/\*)/', $stmt);
        }
    );

    echo "开始执行SQL语句（共 " . count($statements) . " 条）...\n\n";

    $executed = 0;
    $errors = [];

    foreach ($statements as $index => $statement) {
        // 跳过DELIMITER语句
        if (preg_match('/^DELIMITER/', $statement)) {
            echo "  [跳过] DELIMITER语句\n";
            continue;
        }

        // 跳过空语句
        if (trim($statement) === '') {
            continue;
        }

        try {
            $pdo->exec($statement);
            $executed++;
            echo "  ✓ 第 " . ($index + 1) . " 条语句执行成功\n";
        } catch (PDOException $e) {
            $error = "第 " . ($index + 1) . " 条语句执行失败: " . $e->getMessage();
            echo "  ✗ $error\n";
            $errors[] = $error;
        }
    }

    echo "\n==========================================\n";
    echo "  执行结果\n";
    echo "==========================================\n\n";
    echo "成功执行: $executed 条语句\n";

    if (empty($errors)) {
        echo "✓ 所有语句执行成功！\n\n";

        // 验证添加的字段
        echo "验证字段添加结果:\n";
        $stmt = $pdo->query("
            SELECT column_name
            FROM information_schema.columns
            WHERE table_schema = '$database'
            AND table_name = 'oa_customer'
            AND column_name IN ('is_member', 'contract_num', 'case_num')
            ORDER BY column_name
        ");

        $fields = $stmt->fetchAll(PDO::FETCH_COLUMN);
        if (count($fields) === 3) {
            echo "✓ 所有字段已成功添加: " . implode(', ', $fields) . "\n\n";
        } else {
            echo "✗ 部分字段未添加: " . implode(', ', $fields) . "\n\n";
        }

        // 验证触发器
        echo "验证触发器创建结果:\n";
        $stmt = $pdo->query("
            SELECT trigger_name
            FROM information_schema.triggers
            WHERE trigger_schema = '$database'
            AND event_object_table IN ('contract', 'lawcase')
            ORDER BY trigger_name
        ");

        $triggers = $stmt->fetchAll(PDO::FETCH_COLUMN);
        echo "✓ 已创建 " . count($triggers) . " 个触发器:\n";
        foreach ($triggers as $trigger) {
            echo "  - $trigger\n";
        }
        echo "\n";

        // 统计数据
        echo "数据统计:\n";
        $stmt = $pdo->query("
            SELECT
                COUNT(*) AS total_customers,
                SUM(is_member = 1) AS member_customers,
                SUM(contract_num > 0) AS customers_with_contracts,
                SUM(case_num > 0) AS customers_with_cases
            FROM oa_customer
            WHERE delete_time = 0
        ");

        $stats = $stmt->fetch();
        echo "  总客户数: " . number_format($stats['total_customers']) . "\n";
        echo "  会员客户数: " . number_format($stats['member_customers']) . "\n";
        echo "  有合同客户数: " . number_format($stats['customers_with_contracts']) . "\n";
        echo "  有案件客户数: " . number_format($stats['customers_with_cases']) . "\n";
        echo "\n";

        echo "==========================================\n";
        echo "  优化完成！\n";
        echo "==========================================\n\n";
        echo "下一步:\n";
        echo "1. 更新后端Customer模型（使用优化后的查询）\n";
        echo "2. 测试客户列表API\n";
        echo "3. 验证前端显示\n\n";

    } else {
        echo "✗ 执行过程中出现 " . count($errors) . " 个错误:\n";
        foreach ($errors as $error) {
            echo "  - $error\n";
        }
        echo "\n";
    }

} catch (Exception $e) {
    echo "\n✗ 执行失败: " . $e->getMessage() . "\n";
    echo "堆栈信息: " . $e->getTraceAsString() . "\n";
    exit(1);
}
